package dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Component;

import pojo.Customer;
import dao.CustomerDao;

@Component(value="customerdao")
public class CustomerDaoImpl implements CustomerDao{

	@Autowired
	private DataSource ds;
	
	@Override
	public Customer addCustomer(Customer c) {
		String sql="insert into cm (id,cname,sal,birth,sex) values(?,?,?,?,?)";
		Connection con=null;
		PreparedStatement pst=null;
		try {
			con=ds.getConnection();
			pst=con.prepareStatement(sql);
			pst.setString(1, c.getId());
			pst.setString(2, c.getCname());
			pst.setDouble(3, c.getSal());
			pst.setDate(4, new Date(c.getBirth().getTime()));
			pst.setBoolean(5, c.isSex());
			pst.executeUpdate();
		} catch (SQLException e) {			
			e.printStackTrace();
		}
		
		close(con, pst, null);		
		return c;
	}

	@Override
	public Customer updateCustomer(Customer c) {
		String sql="update cm set cname=?,sal=?,birth=?,sex=? where id=?";
		Connection con=null;
		PreparedStatement pst=null;
		try {
			con=ds.getConnection();
			pst=con.prepareStatement(sql);
			pst.setString(1, c.getCname());
			pst.setDouble(2, c.getSal());
			pst.setDate(3, new Date(c.getBirth().getTime()));
			pst.setBoolean(4, c.isSex());
			pst.setString(5, c.getId());
			pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		close(con, pst, null);
		return c;
	}

	@Override
	public List<Customer> findAllCustomers() {
		String sql="select * from cm";
		Connection con=null;
		PreparedStatement pst=null;
		ResultSet rs=null;
		List<Customer> list=new ArrayList<Customer>();
		try {
			con=ds.getConnection();
			pst=con.prepareStatement(sql);
			rs=pst.executeQuery();
			while (rs.next()) {
				Customer c=new Customer(rs.getString("id"), rs.getString("cname"), rs.getDouble("sal"), rs.getBoolean("sex"), rs.getDate("birth"));
				list.add(c);				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		close(con, pst, rs);
		return list;
	}

	@Override
	public String deleteCustomer(String id) {
		String sql="delete from cm where id=?";
		Connection con=null;
		PreparedStatement pst=null;
		try {
			con=ds.getConnection();
			pst=con.prepareStatement(sql);
			pst.setString(1, id);
			pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		close(con, pst, null);
		return id;
	}

	public void close (Connection con,PreparedStatement stat,ResultSet rs){
		try {
			if (rs != null) {
				rs.close();

			}
			if (stat != null) {
				stat.close();
			}
			if (con != null) {
				con.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}
}
